package cn.smile.excel.read;


import cn.smile.annotations.ExcelColumn;
import cn.smile.annotations.utils.AnnotationTool;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *
 * excel处理对象
 * @author 涂航
 */
public class ExcelTemplate<T>{

    /**
     * 目标实体字节码对象
     */
    private Class<T> targetClass;
    /**
     * 读取excel默认读取第一个索引的工作空间
     */
    private Integer defaultSheetIndex = 0;

    /**
     * 装载实体属性中ExcelColumn注解name值 与 属性对象的键值map
     */
    private Map<String, Field> params = null;

    /**
     * 装载列索引与属性对象的键值map
     */
    private Map<Integer, Field> fieldToIndex = null;
    /**
     * 读取Excel文档对象
     */
    private XSSFWorkbook xssfWorkbook = null;
    /**
     * 获取要解析的表格
     */
    private XSSFSheet sheet = null;




    public ExcelTemplate(Class<T> targetClass){
        this.targetClass = targetClass;
        this.params = getExcelColumnAnToField();
    }

    public ExcelTemplate(InputStream inputStream, String sheetName,Class<T> targetClass){
        this(targetClass);
        this.getExcelSheet(inputStream,sheetName);
    }

    public ExcelTemplate(String path, String sheetName,Class<T> targetClass){
        this(targetClass);
        this.getExcelSheet(path,sheetName);
    }

    public ExcelTemplate(InputStream inputStream, Integer index,Class<T> targetClass){
        this(targetClass);
        this.defaultSheetIndex = index;
        this.getExcelSheet(inputStream);
    }

    public ExcelTemplate(InputStream inputStream,Class<T> targetClass){
        this(targetClass);
        this.getExcelSheet(inputStream);
    }

    public ExcelTemplate(String path,Class<T> targetClass){
        this(targetClass);
        this.getExcelSheet(path);
    }




    public List<T> readExcel(){
        try {
            return readExcelRows();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 重载方法
     * @param clazz 对应的实体的字节码对象
     * @param inputStream excel文件流
     * @param <T>对应的实体
     * @return
     */
   /* public <T> List<T> readExcel(Class<T> clazz, InputStream inputStream){
        return  readExcel(clazz,inputStream,"");
    }*/


    /**
     * 将excel数据封装到 对应的实体中 返回实体集合
     * @param clazz 对应的实体的字节码对象
     * @param inputStream excel文件流
     * @param sheetName excel工作空间
     * @param <T> 对应的实体
     * @return
     */
/*    public  <T> List<T> readExcel(Class<T> clazz, InputStream inputStream, String sheetName){
        List<T> list = null;
        try {
            this.getExcelSheet(inputStream,sheetName);
            list = readExcelRows(this.sheet,clazz);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return list;
    }*/


    /**
     * 获取指定的工作空间
     * @param inputStream
     * @param sheetName
     */
    private void getExcelSheet(InputStream inputStream, String sheetName) {
        try {
            this.xssfWorkbook = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        if(sheetName != null&&!sheetName.equals("")){
            //以工作空间名找到解析的表格
            this.sheet = this.xssfWorkbook.getSheet(sheetName);
        }else {
            //以默认索引找到解析的表格
            this.sheet = this.xssfWorkbook.getSheetAt(defaultSheetIndex);
        }
    }

    /**
     * 重载方法
     * 获取指定的工作空间
     * @param path
     * @param sheetName
     */
    private void getExcelSheet(String path, String sheetName) {
        try {
            this.getExcelSheet(new FileInputStream(path),sheetName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 重载方法
     * 获取指定的工作空间
     * @param path
     */
    private void getExcelSheet(String path) {
        try {
            this.getExcelSheet(new FileInputStream(path));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 重载方法
     * 获取指定的工作空间
     * @param inputStream
     */
    private void getExcelSheet(InputStream inputStream) {
        this.getExcelSheet(inputStream,"");
    }



    /**
     * 将excel中所有的数据行转换成实体 封装到集合中
     * @return
     * @throws InvocationTargetException
     * @throws IntrospectionException
     * @throws InstantiationException
     * @throws ParseException
     * @throws IllegalAccessException
     */
    private List<T> readExcelRows() throws InvocationTargetException, IntrospectionException, InstantiationException, ParseException, IllegalAccessException {
        List<T> list = new ArrayList<T>();
         //行对象标识
        XSSFRow row = null;
        //获得最后一行的行号
        int lastRowNum = this.sheet.getLastRowNum();
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            row = this.sheet.getRow(rowNum);
            if(rowNum == 0){
                this.fieldToIndex = readExcelTopFirstRow(row, params);
            }else {
                T t = readExcelRow(row);
                //加入集合
                list.add(t);
            }
        }
        return list;
    }




    /**
     * 处理每一行读取的元素封装成实体
     * @param row 行元素
     * @return
     * @throws IntrospectionException
     * @throws ParseException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws InvocationTargetException
     */
    private T readExcelRow(XSSFRow row) throws IntrospectionException, ParseException, IllegalAccessException, InstantiationException, InvocationTargetException {
        T t = this.targetClass.newInstance();
        //列对象标识
        XSSFCell cell = null;
        //获取最后一列的列号
        int lastCellNum = row.getLastCellNum();
        for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
            //判断该单元格属性值是否为空 需要先进行处理
            cell = row.getCell(cellNum);
            //根据索引获取对应的属性对象
            Field field = fieldToIndex.get(cellNum);
            //获取属性描述器
            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), this.targetClass);
            //获取属性对应的写方法
            Method method = pd.getWriteMethod();
            //获取属性的对应的类型
            Class<?> propertyType = pd.getPropertyType();
            //判断属性类型进行相应的处理
            if(propertyType.equals(Date.class)){
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                method.invoke(t,new SimpleDateFormat(annotation.format()).parse(String.valueOf(cell.getStringCellValue())));
            }else {
                //执行写方法 将数据写入实体对象
                method.invoke(t,typeToConversion(propertyType,cell));
            }
        }
        return t;
    }

    /**
     * 数据类型转换
     * 由于excel有自带的几种数据格式 而且和实体属性的数据格式转换会出现一些问题
     * 所以 必须要考虑 excel数据 将数字类型的数据 以文本格式或者数值格式的方式进行处理
     * @param propertyType 属性类型字节码
     * @param cell 单元格
     * @return
     */
    private Object typeToConversion(Class<?> propertyType,XSSFCell cell){
        Object obj = null;
        if (propertyType.equals(Integer.class)||propertyType.equals(int.class)){
            if(cell.getCellType()== CellType.NUMERIC){
                obj = (int)cell.getNumericCellValue();
            }else {
                obj = Integer.parseInt(cell.getStringCellValue());
            }
        }else if (propertyType.equals(Double.class)||propertyType.equals(double.class)){
            if(cell.getCellType()==CellType.NUMERIC){
                obj = cell.getNumericCellValue();
            }else {
                obj = Double.parseDouble(cell.getStringCellValue());
            }
        }else if (propertyType.equals(Float.class)||propertyType.equals(float.class)){
            if(cell.getCellType()==CellType.NUMERIC){
                obj= cell.getNumericCellValue();
            }else {
                obj = Float.parseFloat(cell.getStringCellValue());
            }
        }else {
            obj = cell.getStringCellValue();
        }
        return obj;
    }

    /**
     * 获取第一行的eccel头的数据
     * 根据列头返回该列的索引 0,1,2... 和实体对应属性对象的 键值map
     * @param row excel中第一行的数据表头
     * @param params 第一行数据中的每一列的名称和对应实体中属性
     * @return
     */
    private Map<Integer,Field> readExcelTopFirstRow(XSSFRow row,Map<String, Field> params){
        HashMap<Integer,Field> fileldToIndex = new HashMap<Integer,Field>();
        int lastCellNum = row.getLastCellNum();
        //获取当前第一行的元素 也就是excel的列头
        for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
            XSSFCell  cell = row.getCell(cellNum);
            Field field = params.get(cell.getStringCellValue());
            if(field == null) {
                continue;
            }
            fileldToIndex.put(cellNum,field);
        }
        return fileldToIndex;
    }

    /**
     * 通过实体属性使用注解来标识excel对应的列
     * @return
     */
    private Map<String,Field> getExcelColumnAnToField(){
        Map<String, Field> fieldToColumn = new HashMap<String, Field>();
        for (Field declaredField : AnnotationTool.getFields(this.targetClass)) {
            ExcelColumn excelColumn = declaredField.getAnnotation(ExcelColumn.class);
            if(excelColumn != null&&!excelColumn.value().equals("NONE")){
                fieldToColumn.put(excelColumn.value(),declaredField);
            }
        }
        return fieldToColumn;
    }


    public XSSFWorkbook getXssfWorkbook() {
        return xssfWorkbook;
    }

    public void setXssfWorkbook(XSSFWorkbook xssfWorkbook) {
        this.xssfWorkbook = xssfWorkbook;
    }

    public XSSFSheet getSheet() {
        return sheet;
    }
}
